﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace SqliteTest1
{
    public class Program
    {
        static string databasePath = @"F:\1-个人文件\10-myGiteeCode\wpfcon-sqllite\SqliteTest1\SqliteTest1\Example\MyDatabase.sqlite";
        
        string connectionString = $"Data Source={databasePath};Version=3;";

        static void Main(string[] args)
        {
            var p = new Program();
        }

        public Program()
        {
            CreateNewDatabase();
            createTable();
            filleTabel();
        }

        void CreateNewDatabase()
        {
            if (!File.Exists(databasePath))
            {
                SQLiteConnection.CreateFile(databasePath);
            }
        }

        void createTable()
        {
            var sql = "CREATE TABLE IF NOT EXISTS highscores (id INTEGER PRIMARY KEY AUTOINCREMENT, name VARCHAR(20), score INTEGER)";

            using (var wrapper = new SQLiteCommandWrapper(connectionString, sql))
            {
                wrapper.Command.ExecuteNonQuery();
            }
        }

        void filleTabel()
        {
            var sql = "INSERT INTO highscores (name, score) VALUES ('Me', 3000), ('Myself', 6000);";
            
            using (var wrapper = new SQLiteCommandWrapper(connectionString, sql))
            {
                wrapper.Command.ExecuteNonQuery();
            }
        }

        public DataSet GetAsDataTable()
        {
            string sql = "SELECT * FROM highscores ORDER BY id ASC";
            string tableName = "mytabel01";

            using (var wrapper = new SQLiteDataAdapterWrapper(connectionString, sql))
            {
                return wrapper.ExecuteQueryAndFillDataSet(tableName);
            }
        }
        public int SaveChangesToDatabase(DataSet dataSet, string tableName)
        {
            string sql = "SELECT * FROM highscores";  

            using (var wrapper = new SQLiteDataAdapterWrapper(connectionString, sql))
            {
                int updatedRows = wrapper.UpdateDataSet(dataSet, tableName);
                return updatedRows; 
            }
        }
    }
}
